package com.module.ble.db

import androidx.room.Dao
import androidx.room.Delete
import androidx.room.Insert
import androidx.room.OnConflictStrategy
import androidx.room.Query
import androidx.room.RawQuery
import androidx.room.Update
import androidx.sqlite.db.SupportSQLiteQuery
import com.module.ble.db.table.UserHeartRate
import kotlinx.coroutines.flow.Flow

/**
 * 作者：sosou
 * <p>
 * 版本：1.0
 * <p>
 * 创建日期：2024/10/18
 * <p>
 * 描述：
 * <p>
 * 修订历史：
 */
@Dao
interface HeartRateDao {


//    @Insert(onConflict = OnConflictStrategy.REPLACE)//当发生主键或唯一约束冲突时， 删除旧记录，插入新记录（保留新数据，适用于需要更新数据的场景）
    @Insert(onConflict = OnConflictStrategy.IGNORE)//当发生主键或唯一约束冲突时， 忽略新记录，保留原有记录（保留旧数据，防止重复插入，保护现有数据）
    fun insert(vararg bean: UserHeartRate)

    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(mList:List<UserHeartRate>)


    @Query("SELECT createdTime FROM heart_rate WHERE createdTime IN (:times)")
    fun getExistingTimes(times: List<Long>): List<Long>

    @Query("SELECT * FROM heart_rate")
    fun findAll(): List<UserHeartRate>

    @Query("SELECT * FROM heart_rate WHERE sysStat = 0")
    fun findAllNotUp(): List<UserHeartRate>

    @Query("SELECT * FROM heart_rate")
    fun findAllFlow(): Flow<List<UserHeartRate>>

    // 先获取最新日期，再查询该日期的所有数据
    @Query("SELECT * FROM heart_rate WHERE date(createdTime/1000, 'unixepoch', 'localtime') = (SELECT date(MAX(createdTime)/1000, 'unixepoch', 'localtime') FROM heart_rate) ORDER BY createdTime DESC")
    fun findLatestDayRecords(): List<UserHeartRate>

    @Query("SELECT * FROM heart_rate WHERE date(createdTime/1000, 'unixepoch', 'localtime') = (SELECT date(MAX(createdTime)/1000, 'unixepoch', 'localtime') FROM heart_rate) ORDER BY createdTime DESC")
    fun findLatestDayFlow(): Flow<List<UserHeartRate>>

    //根据时间范围查询心率数据
    @Query("SELECT * FROM heart_rate WHERE createdTime >= :startTime AND createdTime <= :endTime ORDER BY createdTime ASC")
    fun findByTimeRange(startTime: Long, endTime: Long): List<UserHeartRate>

    // 根据时间范围查询心率数据（Flow版本）
    @Query("SELECT * FROM heart_rate WHERE createdTime >= :startTime AND createdTime <= :endTime ORDER BY createdTime ASC")
    fun findByTimeRangeFlow(startTime: Long, endTime: Long): Flow<List<UserHeartRate>>

    // 查询 所有  倒序 查询
    @Query("SELECT * FROM heart_rate ORDER BY createdTime DESC  LIMIT :mSize")
    fun findByDescSize(mSize: Int): List<UserHeartRate>

    // 根据传入时间戳查询该时间戳当天的所有数据
    @Query("SELECT * FROM heart_rate WHERE date(createdTime/1000, 'unixepoch', 'localtime') = date(:timestamp/1000, 'unixepoch', 'localtime') ORDER BY createdTime ASC")
    fun findByDay(timestamp: Long): List<UserHeartRate>

    // 根据传入时间戳查询该时间戳当天的所有数据（Flow版本）
    @Query("SELECT * FROM heart_rate WHERE date(createdTime/1000, 'unixepoch', 'localtime') = date(:timestamp/1000, 'unixepoch', 'localtime') ORDER BY createdTime ASC")
    fun findByDayFlow(timestamp: Long): Flow<List<UserHeartRate>>

    @RawQuery
    fun findByCustomQuery(query: SupportSQLiteQuery): List<UserHeartRate>

    @RawQuery(observedEntities = [UserHeartRate::class])
    fun findByCustomQueryFlow(query: SupportSQLiteQuery): Flow<List<UserHeartRate>>

    /**
     * 根据指定时间戳查询该时间戳当天及前6天的所有数据
     * @param timestamp 指定的时间戳（毫秒）
     * @return 7天内的睡眠数据列表
     */
    @Query("SELECT * FROM heart_rate WHERE date(createdTime/1000, 'unixepoch', 'localtime') >= date(:timestamp/1000 - 6*24*3600, 'unixepoch', 'localtime') AND date(createdTime/1000, 'unixepoch', 'localtime') <= date(:timestamp/1000, 'unixepoch', 'localtime') ORDER BY createdTime ASC")
    fun findByTimestampLast7Days(timestamp: Long): List<UserHeartRate>

    /**
     * 根据指定时间戳查询该时间戳当天及前6天的所有数据（Flow版本）
     * @param timestamp 指定的时间戳（毫秒）
     * @return Flow<List<UserHeartRate>>
     */
    @Query("SELECT * FROM heart_rate WHERE date(createdTime/1000, 'unixepoch', 'localtime') >= date(:timestamp/1000 - 6*24*3600, 'unixepoch', 'localtime') AND date(createdTime/1000, 'unixepoch', 'localtime') <= date(:timestamp/1000, 'unixepoch', 'localtime') ORDER BY createdTime ASC")
    fun findByTimestampLast7DaysFlow(timestamp: Long): Flow<List<UserHeartRate>>

    /**
     * 根据指定时间戳查询该时间戳前7天的所有数据（不包含今天）
     * @param timestamp 指定的时间戳（毫秒）
     * @return 前7天内的睡眠数据列表
     */
    @Query("SELECT * FROM heart_rate WHERE date(createdTime/1000, 'unixepoch', 'localtime') >= date(:timestamp/1000 - 7*24*3600, 'unixepoch', 'localtime') AND date(createdTime/1000, 'unixepoch', 'localtime') < date(:timestamp/1000, 'unixepoch', 'localtime') ORDER BY createdTime ASC")
    fun findByTimestampLast7DaysNotTheDay(timestamp: Long): List<UserHeartRate>

    /**
     * 根据指定时间戳查询该时间戳前7天的所有数据（不包含今天）（Flow版本）
     * @param timestamp 指定的时间戳（毫秒）
     * @return Flow<List<UserHeartRate>>
     */
    @Query("SELECT * FROM heart_rate WHERE date(createdTime/1000, 'unixepoch', 'localtime') >= date(:timestamp/1000 - 7*24*3600, 'unixepoch', 'localtime') AND date(createdTime/1000, 'unixepoch', 'localtime') < date(:timestamp/1000, 'unixepoch', 'localtime') ORDER BY createdTime ASC")
    fun findByTimestampLast7DaysNotTheDayFlow(timestamp: Long): Flow<List<UserHeartRate>>

    /**
     * 根据传入时间戳查询该时间戳当天内最新的那条数据
     * @param timestamp 指定的时间戳（毫秒）
     * @return 当天最新的心率数据，如果没有数据则返回null
     */
    @Query("SELECT * FROM heart_rate WHERE date(createdTime/1000, 'unixepoch', 'localtime') = date(:timestamp/1000, 'unixepoch', 'localtime') ORDER BY createdTime DESC LIMIT 1")
    fun findLatestByDay(timestamp: Long): UserHeartRate?

    /**
     * 根据传入时间戳查询该时间戳当天内最新的那条数据（Flow版本）
     * @param timestamp 指定的时间戳（毫秒）
     * @return Flow<UserHeartRate?> 当天最新的心率数据
     */
    @Query("SELECT * FROM heart_rate WHERE date(createdTime/1000, 'unixepoch', 'localtime') = date(:timestamp/1000, 'unixepoch', 'localtime') ORDER BY createdTime DESC LIMIT 1")
    fun findLatestByDayFlow(timestamp: Long): Flow<UserHeartRate?>

    @Update
    fun update(data: UserHeartRate)

    @Update
    fun update(mList:List<UserHeartRate>)

    @Query("select count(*) from heart_rate")
    fun count(): Int

    //查询某条数据
    @Query("SELECT * FROM heart_rate WHERE createdTime = :time")
    fun findMessageByTime(time:Long): UserHeartRate

    @Delete
    fun delete(data: UserHeartRate)

    //删除所有 → '所有'的只能用@Query,@Query可以编写任意的sql语句
    @Query("DELETE FROM heart_rate")
    fun deleteAll()

    @Query("DELETE FROM heart_rate WHERE createdTime = :time")
    fun deleteMsgByTime(time:Long)

    /**
     * 将所有数据的sysStat更新为0
     */
    @Query("UPDATE heart_rate SET sysStat = 0")
    fun updateAllSysStatToZero()

    /**
     * 将所有数据的firmName更新为1
     */
    @Query("UPDATE heart_rate SET firmName = '1'")
    fun updateAllFirmNameToOne()

    /**
     * 删除所有heartRate为-1的数据
     */
    @Query("DELETE FROM heart_rate WHERE heartRate = -1")
    fun deleteInvalidHeartRateData()
}